﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
//using Excel = Microsoft.Office.Interop.Excel;
using System.Xml;
using System.Data.OleDb;
using System.IO;
//using System.Runtime.Serialization.Formatters.Binary;

namespace InfoGenerateSystem.Component
{
    /// <summary>
    /// 使用OLEDB导出Excel组件类
    /// 
    /// 创建人:     王昊
    /// 创建时间:   2009-04-28
    /// 
    /// </summary>
    public static class ExcelExporter
    {
        public static void ExportByOledb(DataSet data, String excelFileName)
        {
            System.IO.File.Delete(excelFileName);
            String strConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
            + System.IO.Path.GetDirectoryName(excelFileName) + @"\" +
            System.IO.Path.GetFileName(excelFileName)
            + @";Extended Properties='Excel 8.0;HDR=YES'";

            using (System.Data.OleDb.OleDbConnection objConn = new
            System.Data.OleDb.OleDbConnection(strConnectionString))
            using (System.Data.OleDb.OleDbCommand cmd = new
            System.Data.OleDb.OleDbCommand("", objConn))
            {
                objConn.Open();
                foreach (DataTable dt in data.Tables)
                {
                    cmd.CommandText = "CREATE TABLE [" + dt.TableName + "] (";
                    String valueNames = "(";
                    Boolean first = true;
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (!first)
                        {
                            cmd.CommandText += ",\r\n";
                            valueNames += ", ";
                        }
                        cmd.CommandText += " [" + dc.ColumnName + "] ";

                        if (dc.DataType == typeof(String))
                        {
                            cmd.CommandText += "NVARCHAR(255)";
                        }
                        else if (dc.DataType == typeof(Int16))
                        {
                            cmd.CommandText += "NUMERIC";
                        }
                        else if (dc.DataType == typeof(Int32))
                        {
                            cmd.CommandText += "NUMERIC";
                        }
                        else if (dc.DataType == typeof(Int64))
                        {
                            cmd.CommandText += "NUMERIC";
                        }
                        else if (dc.DataType == typeof(Decimal))
                        {
                            cmd.CommandText += "NUMERIC";
                        }
                        else if (dc.DataType == typeof(Double))
                        {
                            cmd.CommandText += "DOUBLE";
                        }
                        else if (dc.DataType == typeof(DateTime))
                        {
                            cmd.CommandText += "TIMESTAMP";
                        }
                        valueNames += " [" + dc.ColumnName + "]";
                        first = false;
                    }
                    cmd.CommandText += ")";
                    valueNames += ")";
                    cmd.ExecuteNonQuery();
                    foreach (DataRow dr in dt.Rows)
                    {
                        String values = "(";
                        first = true;
                        foreach (DataColumn dc in dt.Columns)
                        {
                            if (!first)
                                values += ", ";

                            if (dr[dc].GetType() != typeof(DBNull))
                            {
                                values += " '";
                                if (dc.DataType == typeof(String)) 
                                {
                                    if (dr[dc].ToString().Length > 255)
                                    {
                                        values += dr[dc].ToString().Substring(0, 250) + "...";
                                    }
                                    else { values += dr[dc]; }
                                }
                                else
                                {
                                    values += dr[dc];
                                }
                                values += "'";
                            }
                            else {
                                values += "NULL";
                            }
                            first = false;
                        }
                        values += ")";
                        cmd.CommandText = "INSERT INTO [" + dt.TableName + "$] " +
                        valueNames + " VALUES " + values;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        ///// <summary>
        ///// 导出Excel
        ///// </summary>
        ///// <param name="dt">要导出的数据</param>
        ///// <param name="isTitle">是否显示表头</param>
        //public static void ExportByOffice(DataTable dt, bool isTitle)
        //{
        //    Excel.ApplicationClass MyExcel = new Excel.ApplicationClass();
        //    MyExcel.Application.Workbooks.Add(true);

        //    Excel._Worksheet sheet = (Excel._Worksheet)MyExcel.Workbooks[1].Worksheets[1];
        //    sheet.Name = "sheet1";

        //    try
        //    {
        //        if (isTitle)
        //        {
        //            for (int i = 0; i < dt.Columns.Count; i++)
        //            {
        //                MyExcel.Cells[1, i + 1] = dt.Columns[i].ColumnName;//生成表头
        //            }
        //            MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Bold = true;
        //            MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Size = 12;
        //            MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置对齐方式
        //            MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Borders.LineStyle = "1";
        //        }

        //        int Rows = 1;
        //        for (int i = 0; i < dt.Rows.Count; i++)
        //        {
        //            Rows += 1;
        //            for (int j = 0; j < dt.Columns.Count; j++)
        //            {
        //                MyExcel.Cells[Rows, j + 1] = "" + dt.Rows[i][j].ToString();//导入数据
        //            }
        //            MyExcel.get_Range(MyExcel.Cells[Rows, 1], MyExcel.Cells[Rows, dt.Columns.Count]).Borders.LineStyle = "1";
        //        }
        //        sheet.Columns.AutoFit();//自动适应宽度
        //        //MyExcel.Visible = true;

        //        //MemoryStream memStream1 = new MemoryStream();
        //        //BinaryFormatter formatter = new BinaryFormatter();
        //        //formatter.Serialize(memStream1, (Object)MyExcel);
        //        //byte[] bytes = memStream1.GetBuffer();
        //    }
        //    catch (Exception ex) { }
        //    finally { }
        //}
    }
}